﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Common;
using CNCSoft.EData;

namespace CNCSoft.ErpApps.TMS.longACSS
{
    public partial class frmDiemDanh : Form
    {
        SqlConnection conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
        DataSet ds = new DataSet();
        string _companyid = GlobalVariable.WSInfo.CompanyID;
        bool _them = false, _edit = false;
        private frmThongTinHocVien frmTTHV = null;

        public frmDiemDanh()
        {
            InitializeComponent();
        }

        private void frmDiemDanh_Load(object sender, EventArgs e)
        {
            LoadControl();
        }

        private void searchLookUpEdit_KhoaHoc_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                LoadMonHocByKhoaHoc(searchLookUpEdit_KhoaHoc.EditValue.ToString());
                searchLookUpEdit_MonHoc.Properties.DataSource = null;
                searchLookUpEdit_MonHoc.Properties.DataSource = ds.Tables["TableMonHocByKhoaHoc"];
                LoadMaGiaoVienByMaKhoaHoc(searchLookUpEdit_KhoaHoc.EditValue.ToString());
                if (ds.Tables["TableMaGiaoVienByMaKhoaHoc"] != null)
                    searchLookUpEdit_GiaoVien.EditValue = ds.Tables["TableMaGiaoVienByMaKhoaHoc"].Rows[0]["MaGiaoVien"].ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_TaoPhieu_Click(object sender, EventArgs e)
        {
            try
            {
                if (Validation())
                {
                    TaoPhieu(searchLookUpEdit_KhoaHoc.EditValue.ToString(), searchLookUpEdit_MonHoc.EditValue.ToString(), Convert.ToDateTime(dateEdit_Ngay.EditValue), 
                                searchLookUpEdit_GiaoVien.EditValue.ToString(), Convert.ToDouble(txt_SoGioDay.Text.Trim()), txt_BaiHoc.Text, memoEdit_Comment.EditValue.ToString(),
                                Convert.ToBoolean(checkEdit_TeacherForgotChecking.EditValue));
                    gridControl_QTHT.DataSource = null;
                    gridControl_QTHT.DataSource = ds.Tables["TableData"];
                    _them = true; _edit = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_XemLai_Click(object sender, EventArgs e)
        {
            try
            {
                if (searchLookUpEdit_KhoaHoc.EditValue == "" || searchLookUpEdit_KhoaHoc.EditValue == null)
                {
                    MessageBox.Show("Bạn chưa chọn lớp hoặc ngày học");

                }
                else
                {
                    XemLai(searchLookUpEdit_KhoaHoc.EditValue.ToString(), Convert.ToDateTime(dateEdit_Ngay.EditValue));
                    gridControl_QTHT.DataSource = null;
                    gridControl_QTHT.DataSource = ds.Tables["TableXemLai"];
                    _them = false; _edit = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_DiemDanh_Click(object sender, EventArgs e)
        {
            try
            {
                if(_them)
                {
                    if(Validation())
                        DiemDanh();
                }
                else if (_edit)
                {
                    UpdateDiemDanh();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gridView_QTHT_RowCellClick(object sender, DevExpress.XtraGrid.Views.Grid.RowCellClickEventArgs e)
        {
            try
            {
                if (e.Column.Name == "gridColumn_HoTen")
                {
                    DataRow dr = gridView_QTHT.GetDataRow(e.RowHandle);
                    frmTTHV = new frmThongTinHocVien(dr["InvoiceNumber"].ToString());
                    frmTTHV.ShowDialog();
                    frmTTHV.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region "Coding"
        private void LoadControl()
        {
            try
            {
                LoadData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadData()
        {
            try
            {
                LoadKhoaHoc();
                searchLookUpEdit_KhoaHoc.Properties.DataSource = ds.Tables["TableKhoaHoc"];
                //------------------------------------------------------
                LoadGiaoVien();
                searchLookUpEdit_GiaoVien.Properties.DataSource = ds.Tables["TableGiaoVien"];
                //------------------------------------------------------
                dateEdit_Ngay.EditValue = DateTime.Now.ToShortDateString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadKhoaHoc()
        {
            try
            {
                if (ds.Tables["TableKhoaHoc"] != null)
                    ds.Tables["TableKhoaHoc"].Clear();
                string sql = @"select * from DM_KHOAHOC";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableKhoaHoc");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadMonHocByKhoaHoc(string makhoahoc)
        {
            try
            {
                if (ds.Tables["TableMonHocByKhoaHoc"] != null)
                    ds.Tables["TableMonHocByKhoaHoc"].Clear();
                string sql = @"select * from DM_MONHOC inner join DM_KHOAHOC on DM_KHOAHOC.MaBangCap = DM_MONHOC.MaBangcap
                                where DM_KHOAHOC.MaKhoaHoc = '" + makhoahoc + "'";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableMonHocByKhoaHoc");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadGiaoVien()
        {
            try
            {
                if (ds.Tables["TableGiaoVien"] != null)
                    ds.Tables["TableGiaoVien"].Clear();
                string sql = @"select * from DM_GIAOVIEN";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableGiaoVien");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadMaGiaoVienByMaKhoaHoc(string makhoahoc)
        {
            try
            {
                if (ds.Tables["TableMaGiaoVienByMaKhoaHoc"] != null)
                    ds.Tables["TableMaGiaoVienByMaKhoaHoc"].Clear();
                string sql = @"select MaGiaoVien from KhoaHoc_GiaoVien where MaKhoaHoc = '"+makhoahoc+@"'";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableMaGiaoVienByMaKhoaHoc");;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void TaoPhieu(string makhoahoc, string mamonhoc, DateTime ngayhoc, string magiaovien, double sogioday, string baihoc, string giaoviencomment, bool forgotchecking)
        {
            try
            {
                int i = 0;
                if (forgotchecking)
                    i = 1;
                else
                    i = 0;
                if (ds.Tables["TableData"] != null)
                    ds.Tables["TableData"].Clear();
                string sql = @"declare @baihoc nvarchar(100), @magiaovien varchar(20), @ngayhoc datetime, 
                                @comat bit, @sogioday float, @mamonhoc varchar(10), @ghichu nvarchar(50),
                                @giaoviencomment nvarchar(200), @teacherforgotchecking bit
                                set @baihoc = '" + baihoc+@"'
                                set @magiaovien = '"+magiaovien+@"'
                                set @ngayhoc = '"+ngayhoc+@"'
                                set @comat = 0
                                set @sogioday = "+sogioday+@"
                                set @mamonhoc = '"+mamonhoc+ @"'
                                set @ghichu = ''
                                set @giaoviencomment = N'"+giaoviencomment+@"'
                                set @teacherforgotchecking = " +i+ @"
                                select distinct inv.InvoiceNumber, (enq.Holot + ' ' + enq.Ten) as 'HoTen',
                                                                @mamonhoc as 'MaMonHoc', 
                                                                @baihoc as 'BaiHoc',
                                                                @magiaovien as 'MaGiaoVien',
                                                                @ngayhoc as 'NgayHoc', 
                                                                @sogioday as 'SoGioDay',
                                                                @comat as 'CoMat',
                                                                @ghichu as 'GhiChu',
                                                                @giaoviencomment as 'GiaoVienComment',
                                                                @teacherforgotchecking as 'TeacherForgotChecking'
                                                                from Invoice_KhoaHoc_QTHT qtht
                                                                right outer join Invoice inv on inv.InvoiceNumber = qtht.InvoiceNumber
                                                                inner join Enquiry enq on enq.EnquiryNumber = inv.EnquiryNumber
                                                                where inv.MaLop = '" + makhoahoc+@"' ";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableData");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void XemLai(string makhoahoc, DateTime ngayhoc)
        {
            try
            {
                if (ds.Tables["TableXemLai"] != null)
                    ds.Tables["TableXemLai"].Clear();
                string sql = @"select  qtht.ID, qtht.InvoiceNumber, (enq.Holot + ' ' + enq.Ten) as 'HoTen',
                                qtht.MaMonHoc, 
                                ISNULL(qtht.BaiHoc, '') as 'BaiHoc',
                                ISNULL(qtht.MaGiaoVien, '') as 'MaGiaoVien',
                                ISNULL(qtht.NgayHoc, GETDATE()) as 'NgayHoc', 
                                ISNULL(qtht.SoGioDay, '') as 'SoGioDay',
                                ISNULL(qtht.CoMat, 0) as 'CoMat',
                                qtht.GhiChu,
                                qtht.GiaoVienComment,
                                qtht.TeacherForgotChecking
                                from Invoice_KhoaHoc_QTHT qtht
                                inner join Invoice inv on inv.InvoiceNumber = qtht.InvoiceNumber
                                inner join Enquiry enq on enq.EnquiryNumber = inv.EnquiryNumber
                                where inv.MaLop = '" + makhoahoc + "' and qtht.NgayHoc = '" + ngayhoc + "'";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableXemLai");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void DiemDanh()
        {
            try
            {
                foreach(DataRow dr in ds.Tables["TableData"].Rows)
                {
                    string sql = @"insert into Invoice_KhoaHoc_QTHT(InvoiceNumber, MaMonHoc, BaiHoc, MaGiaoVien, CoMat, GhiChu, NgayHoc, SoGioDay, GiaoVienComment, TeacherForgotChecking) 
                                    values(@invoicenumber, @mamonhoc, @baihoc, @magiaovien, @comat, @ghichu, @ngayhoc, @sogioday, @giaoviencomment, @teacherforgotchecking)";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@invoicenumber", dr["InvoiceNumber"].ToString());
                    cmd.Parameters.AddWithValue("@mamonhoc", dr["MaMonHoc"].ToString());
                    cmd.Parameters.AddWithValue("@baihoc", dr["BaiHoc"].ToString());
                    cmd.Parameters.AddWithValue("@magiaovien", dr["MaGiaoVien"].ToString());
                    cmd.Parameters.AddWithValue("@comat", Convert.ToBoolean(dr["CoMat"]));
                    cmd.Parameters.AddWithValue("@ghichu", dr["GhiCHu"].ToString());
                    cmd.Parameters.AddWithValue("@ngayhoc", Convert.ToDateTime(dr["NgayHoc"]));
                    cmd.Parameters.AddWithValue("@sogioday", Convert.ToDouble(dr["SoGioDay"]));
                    cmd.Parameters.AddWithValue("@giaoviencomment", memoEdit_Comment.EditValue.ToString());
                    cmd.Parameters.AddWithValue("@teacherforgotchecking", Convert.ToBoolean(dr["TeacherForgotChecking"]));
                    int i = 0;
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                            conn.Open();
                        i = cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (conn != null)
                            conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void UpdateDiemDanh()
        {
            try
            {
                foreach (DataRow dr in ds.Tables["TableXemLai"].Rows)
                {
                    string sql = @"update Invoice_KhoaHoc_QTHT set CoMat = @comat, GhiChu = @ghichu, TeacherForgotChecking = @teacherforgotchecking  
                                    where ID = " + Convert.ToInt32(dr["ID"]);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    //cmd.Parameters.AddWithValue("@mamonhoc", dr["MaMonHoc"].ToString());
                    //cmd.Parameters.AddWithValue("@baihoc", dr["BaiHoc"].ToString());
                    //cmd.Parameters.AddWithValue("@magiaovien", dr["MaGiaoVien"].ToString());
                    cmd.Parameters.AddWithValue("@comat", Convert.ToBoolean(dr["CoMat"]));
                    cmd.Parameters.AddWithValue("@ghichu", dr["GhiCHu"].ToString());
                    cmd.Parameters.AddWithValue("@teacherforgotchecking", Convert.ToBoolean(dr["TeacherForgotChecking"]));
                    //cmd.Parameters.AddWithValue("@ngayhoc", Convert.ToDateTime(dr["NgayHoc"]));
                    //cmd.Parameters.AddWithValue("@sogioday", Convert.ToDouble(dr["SoGioDay"]));
                    int i = 0;
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                            conn.Open();
                        i = cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        if (conn != null)
                            conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private bool Validation()
        {
            try
            {
                if (searchLookUpEdit_KhoaHoc.EditValue == "" || searchLookUpEdit_KhoaHoc.EditValue == null)
                {
                    MessageBox.Show("Bạn chưa chọn lớp.");
                    searchLookUpEdit_KhoaHoc.Focus();
                    return false;
                }
                else if (searchLookUpEdit_MonHoc.EditValue == "" || searchLookUpEdit_MonHoc.EditValue == null)
                {
                    MessageBox.Show("Bạn chưa chọn môn học.");
                    searchLookUpEdit_MonHoc.Focus();
                    return false;
                }
                else if (searchLookUpEdit_GiaoVien.EditValue == "" || searchLookUpEdit_GiaoVien.EditValue == null)
                {
                    MessageBox.Show("Bạn chưa chọn giáo viên.");
                    searchLookUpEdit_GiaoVien.Focus();
                    return false;
                }
                else if (txt_SoGioDay.Text == "")
                {
                    MessageBox.Show("Bạn chưa nhập số giờ dạy.");
                    txt_SoGioDay.Focus();
                    return false;
                }
                else if (txt_BaiHoc.Text == "")
                {
                    MessageBox.Show("Bạn chưa nhập tên bài học.");
                    txt_BaiHoc.Focus();
                    return false;
                }
                else if (memoEdit_Comment.EditValue == "" || memoEdit_Comment.EditValue == null)
                {
                    MessageBox.Show("Bạn chưa ghi comment của giáo viên.");
                    memoEdit_Comment.Focus();
                    return false;
                }
                bool e = false;
                LoadMaGiaoVienByMaKhoaHoc(searchLookUpEdit_KhoaHoc.EditValue.ToString());
                foreach (DataRow r in ds.Tables["TableMaGiaoVienByMaKhoaHoc"].Rows)
                {
                    if (r["MaGiaoVien"].ToString() == searchLookUpEdit_GiaoVien.EditValue.ToString())
                        e = true;
                    else
                        e = false;
                }
                if (!e)
                {
                    MessageBox.Show("Giáo viên này chưa được đăng ký cho lớp này! Xin mời bạn đăng ký trước cho giáo viên này.");
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        #endregion
    }
}
